DML Country

Probleem

We moeten de gegevens van een land kunnen inserten, updaten, deleten en selecteren. De selectie moet kunnen gebeuren op basis van de naam en van de code.

Design

Oplossing

Insert

De Insert stored procedure voegt een nieuwe rij toe. In de DDL hebben we de Id kolom gedeclareerd als een IDENTITY(1,1) kolom. Dat wil zeggen dat de waarde met 1 wordt vermeerderd als we een rij toevoegen. Als vanuit C# of PHP een call maken naar deze procedure kan het nodig zijn om de nieuwe Id te kennen. Vermits het een stored procedure is en geen functie, kan deze geen waarde retourneren en hebben we een OUTPUT parameter nodig om de waarde van de nieuwe idee naar C# of PHP terug te sturen. In de parameterlijst voegen we dus een OUTPUT parameter toe met de naam Id waarin we de waarde van de Id kolom stoppen van de nieuw toegevoegde rij.

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Sunday 3rd of January 2016 02:28:16 PM
-- DML Insert Stored Procedure for Country 
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'CountryInsert'))
BEGIN
    DROP PROCEDURE CountryInsert
END
GO
CREATE PROCEDURE "CountryInsert"
(
    @Code NVARCHAR (2) ,
    @Latitude FLOAT ,
    @Longitude FLOAT ,
    @Name NVARCHAR (255) ,
    @ShippingCostMultiplier FLOAT ,
    @Id INT  output
)
AS
BEGIN
INSERT INTO "Country"
    (
        "Country"."Code",
        "Country"."Latitude",
        "Country"."Longitude",
        "Country"."Name",
        "Country"."ShippingCostMultiplier"
    )
    VALUES
    (
        @Code,
        @Latitude,
        @Longitude,
        @Name,
        @ShippingCostMultiplier
    );
    set @Id = SCOPE_IDENTITY();
END 
GO

We proberen de Insert stored procedure uit. We beginnen met een variabele te declareren die de output parameter van de stored procedure moet opvangen en vervolgens tonen we de waarde ervan met de PRINT instructie. Als we die test twee keer na elkaar uitvoeren, krijgen we een foutmelding omdat we geen twee keer dezelfde code en/of naam kunnen toevoegen. Dat komt door de UNIQUE constraint op de kolom Code en Name.

declare @NewId int

exec CountryInsert 'BE', 56, 50.8333, 'Belgium', 1, @NewId OUTPUT
print @NewId
exec CountryInsert 'BZ',84, 17.25, 'Belize', 1.5, @NewId OUTPUT
print @NewId
exec CountryInsert 'BJ', 204, 9.5, 'Benin', 1.5, @NewId OUTPUT
print @NewId
exec CountryInsert 'BM', 60, 32.3333, 'Bermuda', 1.5, @NewId OUTPUT
print @NewId
exec CountryInsert 'BT', 64, 27.5, 'Bhutan', 1.5, @NewId OUTPUT
print @NewId
exec CountryInsert 'BO', 68, -17, 'Bolivia; Plurinational State of', 1.5, @NewId OUTPUT
print @NewId
exec CountryInsert 'BA', 70, 44, 'Bosnia and Herzegovina', 1.5, @NewId OUTPUT
print @NewId
exec CountryInsert 'BW', 72, -22, 'Botswana', 1.5, @NewId OUTPUT
print @NewId
exec CountryInsert 'BV', 74, -54.4333, 'Bouvet Island', 1.5, @NewId OUTPUT
print @NewId
exec CountryInsert 'BR', 76, -10, 'Brazil', 1.5, @NewId OUTPUT

Update

De Update stored procedure neemt de Id van de rij die geüpdated moet worden als argument.

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Sunday 3rd of January 2016 02:28:16 PM
-- DML Update Stored Procedure for Country
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'CountryUpdate'))
BEGIN
    DROP PROCEDURE CountryUpdate
END
GO
CREATE PROCEDURE "CountryUpdate"
(
    @Code NVARCHAR (2) ,
    @Latitude FLOAT ,
    @Longitude FLOAT ,
    @Name NVARCHAR (255) ,
    @ShippingCostMultiplier FLOAT ,
    @Id INT 
)
AS
BEGIN
UPDATE "Country"
    SET
        "Code" = @Code,
        "Latitude" = @Latitude,
        "Longitude" = @Longitude,
        "Name" = @Name,
        "ShippingCostMultiplier" = @ShippingCostMultiplier
    WHERE "Country"."Id" = @Id;
END 
GO

Een voorbeeld:

exec CountryUpdate 'BE', 56, 50.8333, 'België', 1.1, 1

Deze stored procedure wijzigt de verzendingskost factor. De rest van de kolommen behouden hun oorspronkelijke waarden, maar je moet ze wel meegeven omdat de stored procedures deze als argumenten verwacht.

Delete

De Delete stored procedure neemt de Id van de rij die gedeleted moet worden als argument.

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Sunday 3rd of January 2016 02:28:16 PM
-- DML Delete Stored Procedure for Country 
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'CountryDelete'))
BEGIN
    DROP PROCEDURE CountryDelete
END
GO
CREATE PROCEDURE "CountryDelete"
(
     @Id INT 
)
AS
BEGIN
DELETE FROM "Country"
    WHERE "Country"."Id" = @Id;
END 
GO

Een voorbeeld:

exec CountryDelete 1

We voegen België opnieuw toe. Let erop dat de Id anders is dan de vorige keer:

SelectOne

De SelectOne stored procedure haald de rij met de opgegeven Id uit de tabel op.

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Sunday 3rd of January 2016 02:28:16 PM
-- DML SelectOne Stored Procedure for Country 
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'CountrySelectOne'))
BEGIN
    DROP PROCEDURE CountrySelectOne
END
GO
CREATE PROCEDURE "CountrySelectOne"
(
     @Id INT 
)
AS
BEGIN
SELECT * FROM "Country"
    WHERE "Country"."Id" = @Id;
END 
GO

Zoals altijd testen we de stored procedure:

exec CountrySelectOne 5

SelectAll

Over de SelectAll valt niet veel te zeggen. De stored procedure neemt geen argumenten aan en retourneert alle rijen uit de tabel.

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Sunday 3rd of January 2016 02:28:16 PM
-- DML SelectAll Stored Procedure for table Country 
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'CountrySelectAll'))
BEGIN
    DROP PROCEDURE CountrySelectAll
END
GO
CREATE PROCEDURE "CountrySelectAll"
AS
BEGIN
SELECT "Country"."Code",
    "Country"."Name",
    "Country"."ShippingCostMultiplier",
    "Country"."Id"
    FROM "Country"
    ORDER BY "Name";
END 
GO

SelectByCode

We voegen ook een stored procedure toe om op de Code van het land te zoeken.

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Sunday 3rd of January 2016 02:28:16 PM
-- DML SelectByCode Stored Procedure for table Country
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'CountrySelectByCode'))
BEGIN
    DROP PROCEDURE CountrySelectByCode
END
GO
CREATE PROCEDURE "CountrySelectByCode"
(
     @Code NVARCHAR (2) 
)
AS
BEGIN
SELECT "Country"."Code",
    "Country"."Name",
    "Country"."ShippingCostMultiplier",
    "Country"."Id"

    FROM "Country"
    WHERE "Country"."Code" = @Code
    ORDER BY "Country"."Code";
END 
GO

SelectByName

Voor de volledigheid voegen we tenslotte een stored procedure toe om op de naam van het land te kunnen zoeken.

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Sunday 3rd of January 2016 02:28:16 PM
-- DML SelectByName Stored Procedure for table Country
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'CountrySelectByName'))
BEGIN
    DROP PROCEDURE CountrySelectByName
END
GO
CREATE PROCEDURE "CountrySelectByName"
(
     @Name NVARCHAR (255) 
)
AS
BEGIN
SELECT "Country"."Code",
    "Country"."Name",
    "Country"."ShippingCostMultiplier",
    "Country"."Id"

    FROM "Country"
    WHERE "Country"."Name" = @Name
    ORDER BY "Country"."Name";
END 
GO